﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IDAL;

namespace KPIS.GERP.GHRMS.DAL
{
    public class RetirementDetailDAL : IRetirementDetailDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        
        public RetirementDetailDAL()
        {

        }
        public DataSet LoadRetirementDetails(RetirementDetailInfo reInfo)
        {
            string strSQL;
            int Parent = 0;
            if (reInfo.Parent != null)
                Parent = reInfo.Parent.Sequence;

            DataSet ds = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@Sequence", reInfo.Sequence);
                dbManager.AddParameters(1, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@Parent", Parent);

                strSQL = "SELECT rd.*"
                        + ", CONCAT(np.PREFIX_NAME, e.FIRST_NAME, '  ', e.LAST_NAME) AS FULL_NAME"
                        + ", e.POS_SEQ, p.POS_REF, p.POS_NAME, j.JOB_SITE_NAME, e.CLASS_SEQ, c.CLASS_NAME"
                    + " FROM HRM_PS_RETIREMENT_DTLS rd"
                        + " INNER JOIN HRM_PS_RETIREMENTS r ON rd.RETIRE_SEQ = r.RETIRE_SEQ"
                        + " LEFT JOIN HRM_PS_EMPLOYEES e ON rd.EMP_SEQ = e.EMP_SEQ"
                        + " LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"                        
                        + " LEFT JOIN HRM_MP_CLASSES c ON e.CLASS_SEQ = c.CLASS_SEQ"
                        + " LEFT JOIN HRM_MP_POSITIONS p ON e.POS_SEQ = p.POS_SEQ"
                        + " LEFT JOIN HRM_MP_JOB_SITES j ON p.JOB_SITE_SEQ = j.JOB_SITE_SEQ"
                    + " WHERE rd.RECORD_STATUS not like @DeleteRecord"
                    + (reInfo.Sequence > 0 ? " AND rd.RETIRE_DTLS_SEQ = @Sequence" : "")
                    + (Parent > 0 ? " AND rd.RETIRE_SEQ = @Parent" : "");

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                
                return ds;
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }
 
        public void Insert(RetirementDetailInfo Info)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(6);

                dbManager.AddParameters(0, "@RetireSeq", Info.Parent == null ? (int?)null : Info.Parent.Sequence);
                dbManager.AddParameters(1, "@EmpSeq", Info.Employee == null ? (int?)null : Info.Employee.Sequence);
                dbManager.AddParameters(2, "@Remarks", Info.Remarks);
                dbManager.AddParameters(3, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(4, "@CreatedBy", Info.CreatedBy);
                dbManager.AddParameters(5, "@CreatedWhen", DateTime.Today);

                strSQL = "INSERT INTO HRM_PS_RETIREMENT_DTLS(RETIRE_SEQ, EMP_SEQ, REMARKS"
                        + ", RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                    + " VALUES(@RetireSeq, @EmpSeq, @Remarks"
                        + ", @RecordStatus, @CreatedBy, @CreatedWhen)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(RetirementDetailInfo Info)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(6);

                dbManager.AddParameters(0, "@Sequence", Info.Sequence);
                dbManager.AddParameters(1, "@RetireSeq", Info.Parent == null ? (int?)null : Info.Parent.Sequence);
                dbManager.AddParameters(2, "@EmpSeq", Info.Employee == null ? (int?)null : Info.Employee.Sequence);
                dbManager.AddParameters(3, "@Remarks", Info.Remarks);
                dbManager.AddParameters(4, "@UpdatedBy", Info.UpdatedBy);
                dbManager.AddParameters(5, "@UpdatedWhen", DateTime.Today);

                strSQL = "UPDATE HRM_PS_RETIREMENT_DTLS"
                           + " SET RETIRE_SEQ = @RetireSeq"
                                + ", EMP_SEQ = @EmpSeq"
                                + ", REMARKS = @Remarks"
                                + ", UPDATED_BY = @UpdatedBy"
                                + ", UPDATED_WHEN = @UpdatedWhen"
                           + " WHERE RETIRE_DTLS_SEQ = @Sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(int Sequence, int UpdatedBy)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@UpdatedBy", UpdatedBy);
                dbManager.AddParameters(3, "@UpdateWhen", DateTime.Today);

                strSQL = "UPDATE HRM_PS_RETIREMENT_DTLS"
                    + " SET RECORD_STATUS = @RecordStatus"
                        + ", UPDATED_BY = @UpdatedBy"
                        + ", UPDATED_WHEN = @UpdateWhen"
                    + " WHERE RETIRE_DTLS_SEQ = @Sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
